﻿using MODEL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DAL
{
   public class DriverDAL
    {
        #region 显示驾驶员信息
        /// <summary>
        /// 显示驾驶员信息
        /// </summary>
        /// <param name="drive"></param>
        /// <returns></returns>
        public List<DriverQueryModel> Getdrivrmodel(DriverMODEL model)
        {
            StringBuilder strbuilder = new StringBuilder();
            strbuilder.Append(@"SELECT d.DriverID,d.FK_TeamID,d.Name,
CASE d.Sex WHEN 0 THEN '男' ELSE '女' END  AS Sex,
d.Phone,d.Brith,tt.TeamName,d.IDCard,
CASE d.IsDelete WHEN 0 THEN '是' ELSE '否' END AS IsDelete,
d.CheckInTime,t.Number FROM dbo.Driver AS d INNER JOIN 
dbo.TruckTeam AS tt ON d.FK_TeamID=tt.TeamID INNER JOIN dbo.Truck AS t ON t.FK_TeamID=tt.TeamID
");

            List<string> listwhere = new List<string>();
            List<SqlParameter> listparm = new List<SqlParameter>();
            #region 查询条件
            //姓名
            if (!string.IsNullOrWhiteSpace(model.Name))
            {
                listwhere.Add("Name=@Name");
                listparm.Add(new SqlParameter("@Name", model.Name));
            }
            //性别
            if (model.Sex != -1)
            {
                listwhere.Add("Sex=@Sex");
                listparm.Add(new SqlParameter("@Sex", model.Sex));
            }
            //身份证号码
            if (!string.IsNullOrWhiteSpace(model.IDCard))
            {
                listwhere.Add("IDCard=@IDCard");
                listparm.Add(new SqlParameter("@IDCard", model.IDCard));
            }
            //生日
            if (model.Brith_in != null)
            {
                listwhere.Add("Brith>=@Brith_in");
                listparm.Add(new SqlParameter("@Brith_in", model.Brith_in));
            }
            
            if (model.Brith_end != null)
            {
                listwhere.Add("Brith<=@Brith_end");
                listparm.Add(new SqlParameter("@Brith_end", model.Brith_end));
            }
            //电话
            if (!string.IsNullOrWhiteSpace(model.Phone))
            {
                listwhere.Add("Phone=@Phone");
                listparm.Add(new SqlParameter("@Phone", model.Phone));
            }
            //聘用日期
            if (model.CheckInTime_in != null)
            {
                listwhere.Add("CheckInTime>=@CheckInTime_in");
                listparm.Add(new SqlParameter("@CheckInTime_in", model.CheckInTime_in));
            }
            if (model.CheckInTime_end != null)
            {
                listwhere.Add("CheckInTime<=@CheckInTime_end");
                listparm.Add(new SqlParameter("@CheckInTime_end", model.CheckInTime_end));
            }
            //判断是否有条件
            if (listparm.Count > 0)
            {
                strbuilder.Append($" where {string.Join(" and ", listwhere.ToArray())}");
            }
            #endregion
            DataTable dt = DBhelper.SqlDataAdapter(strbuilder.ToString(), listparm.ToArray());
            List<DriverQueryModel> list = new List<DriverQueryModel>();
            foreach (DataRow dr in dt.Rows)
            {
                DriverQueryModel infomodel = new DriverQueryModel()
                {
                    DriverID = Convert.ToInt32(dr["DriverID"]),
                    FK_TeamID = Convert.ToInt32(dr["FK_TeamID"]),
                    Name = dr["Name"].ToString(),
                    Sex = dr["Sex"].ToString(),
                    IsDelete = dr["IsDelete"].ToString(),
                    Phone = dr["Phone"].ToString(),
                    Brith = Convert.ToDateTime(dr["Brith"]),
                    TeamName = dr["TeamName"].ToString(),
                    IDCard = dr["IDCard"].ToString(),
                    CheckInTime = Convert.ToDateTime(dr["CheckInTime"]),
                    //Remark=dr["Remark"].ToString(),
                    //State=dr["State"].ToString(),
                    Number = dr["Number"].ToString()
                };
                list.Add(infomodel);
            }
            return list;
        }
        #endregion

        #region 添加驾驶员信息
        /// <summary>
        /// 添加驾驶员信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Getdriver_add(DriverMODEL model)
        {
            string sql = @" INSERT INTO dbo.Driver( Name ,Sex ,Brith ,Phone ,IDCard ,FK_TeamID ,State ,
Remark ,CheckInTime ,IsDelete ,AlterTime)VALUES 
(@Name ,@Sex ,@Brith ,@Phone ,@IDCard ,@FK_TeamID ,@State ,@Remark ,@CheckInTime ,@IsDelete ,@AlterTime)";
            SqlParameter[] parms = {
                new SqlParameter("@Name",model.Name),
                new SqlParameter("@Sex",model.Sex),
                new SqlParameter("@Brith",model.Brith),
                new SqlParameter("@Phone",model.Phone),
                new SqlParameter("@IDCard",model.IDCard),
                new SqlParameter("@FK_TeamID",model.FK_TeamID),
                new SqlParameter("@State",model.State),
                new SqlParameter("@Remark",model.Remark),
                new SqlParameter("@CheckInTime",model.CheckInTime),
                new SqlParameter("@IsDelete",false),
                new SqlParameter("@AlterTime",DateTime.Now)
            };
            return DBhelper.ExecuteNonQuery(sql, parms);
        }
        #endregion

        #region 判断添加的驾驶员姓名是否重复
        /// <summary>
        /// 判断添加的驾驶员姓名是否重复
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool Getdriver_name(string name)
        {
            string sql = @"SELECT COUNT(*) FROM dbo.Driver WHERE Name=@Name";
            SqlParameter[] parm = {
                new SqlParameter("@Name",name)
            };
            return Convert.ToInt32(DBhelper.ExecuteScalar(sql, parm)) > 0;
        }
        #endregion

        #region 删除驾驶员信息
        /// <summary>
        /// 删除驾驶员信息
        /// </summary>
        /// <param name="driverid"></param>
        /// <returns></returns>
        public int Getdrive_delete(int driverid)
        {
            string sql = (@"UPDATE dbo.Driver SET IsDelete=0 WHERE DriverID=@DriverID");
            SqlParameter[] parms = {
                new SqlParameter("@DriverID",driverid)
            };
            return DBhelper.ExecuteNonQuery(sql, parms);
        }
        #endregion

        #region 根据ID查询驾驶员删除状态
        /// <summary>
        /// 根据ID查询驾驶员删除状态
        /// </summary>
        /// <param name="driverid"></param>
        /// <returns></returns>
        public DriverMODEL Getdrive_byid(int driverid)
        {
            string sql = @"SELECT * FROM dbo.Driver AS d INNER JOIN dbo.TruckTeam AS tt ON d.FK_TeamID=tt.TeamID
 INNER JOIN dbo.Truck AS t ON t.FK_TeamID = tt.TeamID WHERE DriverID = @DriverID";
            SqlParameter[] pams = {
                new SqlParameter("@DriverID",driverid),
            };
            //通过SqlDataReader游标读取数据，连线式访问数据库
            using (SqlDataReader reader = DBhelper.ExecuteReader(sql, pams))
            {
                //如果有行数，说明有用户信息
                if (reader.HasRows)
                {
                    //使游标前进到下一行
                    if (reader.Read())
                    {
                        DriverMODEL infomodel = new DriverMODEL()
                        {
                            DriverID = Convert.ToInt32(reader["DriverID"]),
                            FK_TeamID = Convert.ToInt32(reader["FK_TeamID"]),
                            Name = reader["Name"].ToString(),
                            Sex = Convert.ToInt32(reader["Sex"]),
                            IsDelete = Convert.ToInt32(reader["IsDelete"]),
                            Phone = reader["Phone"].ToString(),
                            Brith = Convert.ToDateTime(reader["Brith"]),
                            TeamName = reader["TeamName"].ToString(),
                            IDCard = reader["IDCard"].ToString(),
                            State = Convert.ToInt32(reader["State"]),
                            Remark = reader["Remark"].ToString(),
                            CheckInTime = Convert.ToDateTime(reader["CheckInTime"]),
                            Number = reader["Number"].ToString()
                        };
                        return infomodel;
                    }
                }
                return null;
            }
        }
        #endregion

        #region 修改用户信息
        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="driverinfo"></param>
        /// <returns></returns>
        public int getdriver_Update(DriverMODEL driverinfo)
        {
            string update = @"UPDATE dbo.Driver SET Name=@Name,Sex=@Sex,Brith=@Brith,Phone=@Phone,
IDCard=@IDCard,FK_TeamID=@FK_TeamID,State=@State,Remark=@Remark,
CheckInTime=@CheckInTime WHERE DriverID=@DriverID";
            SqlParameter[] parms = {
                new SqlParameter("@Name",driverinfo.Name),
                new SqlParameter("@Sex",driverinfo.Sex),
                new SqlParameter("@Brith",driverinfo.Brith),
                new SqlParameter("@Phone",driverinfo.Phone),
                new SqlParameter("@IDCard",driverinfo.IDCard),
                new SqlParameter("@FK_TeamID",driverinfo.FK_TeamID),
                new SqlParameter("@State",driverinfo.State),
                new SqlParameter("@Remark",driverinfo.Remark),
                new SqlParameter("@CheckInTime",driverinfo.CheckInTime),
                new SqlParameter("@DriverID",driverinfo.DriverID),
            };
            int result = DBhelper.ExecuteNonQuery(update, parms);
            return result;
        }
        #endregion

        #region 驾驶员绑定车辆信息
        /// <summary>
        /// 显示车辆信息
        /// </summary>
        /// <param name="truck"></param>
        /// <returns></returns>
        public List<TruckinfoModel> Gettruckinfo(TruckMODEL truck)
        {
            StringBuilder strbuilder = new StringBuilder();
            strbuilder.Append(@" SELECT t.TruckID,t.Number,t.BuyDate,t.Type, t.Tonngae,tt.TeamName,
CASE t.State WHEN '1' THEN '承运' ELSE '空闲' END  AS State,
CASE t.IsDelete WHEN '0' THEN '是' ELSE '否' END AS IsDelete,
t.CheckInTime,t.AlterTime FROM dbo.Truck AS t INNER JOIN  dbo.TruckTeam AS tt ON tt.TeamID=t.FK_TeamID 
");
            List<string> listwhere = new List<string>();
            List<SqlParameter> listparm = new List<SqlParameter>();
            #region 查询条件
            if (truck.TeamName != "不限")
            {
                listwhere.Add("tt.TeamName=@TeamName");
                listparm.Add(new SqlParameter("@TeamName", truck.TeamName));
            }
            if (!string.IsNullOrWhiteSpace(truck.State))
            {
                listwhere.Add("t.State=@State");
                listparm.Add(new SqlParameter("@State", truck.State));
            }
            if (listparm.Count > 0)
            {
                strbuilder.Append($" where {string.Join(" and ", listwhere.ToArray())}");
            }
            #endregion 
            DataTable dt = DBhelper.SqlDataAdapter(strbuilder.ToString(), listparm.ToArray());
            List<TruckinfoModel> list = new List<TruckinfoModel>();
            foreach (DataRow dr in dt.Rows)
            {
                TruckinfoModel team = new TruckinfoModel()
                {
                    TruckID = Convert.ToInt32(dr["TruckID"]),
                    Number = dr["Number"].ToString(),
                    BuyDate = Convert.ToDateTime(dr["BuyDate"]),
                    Type = dr["Type"].ToString(),
                    Tonngae = Convert.ToInt32(dr["Tonngae"]),
                    TeamName = dr["TeamName"].ToString(),
                    State = dr["State"].ToString(),
                    IsDelete = dr["IsDelete"].ToString(),
                    CheckInTime = Convert.ToDateTime(dr["CheckInTime"]),
                    AlterTime = Convert.ToDateTime(dr["AlterTime"])
                };
                list.Add(team);
            }
            return list;
        }
        #endregion

        #region 绑定显示与隐藏绑定
        /// <summary>
        /// 绑定显示与隐藏绑定
        /// </summary>
        /// <param name="driverid"></param>
        /// <returns></returns>
        public int GetBindCount(int driverid)
        {
            string sql = "SELECT COUNT(*) FROM dbo.Contact WHERE FK_DriverID=@DriverID";
            SqlParameter[] parm = {
                new SqlParameter ("@DriverID",driverid)
            };
            return Convert.ToInt32(DBhelper.ExecuteScalar(sql, parm));
        } 
        #endregion

    }
}
